# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import re
from scrapy import Request
from scrapy.pipelines.images import ImagesPipeline
from ManhuaSpider.items import MhInfoItem, MhImageItem, MhUpdateItem, MhSectionInfoItem, CategoryItem
import MySQLdb
import MySQLdb.cursors
from twisted.enterprise import adbapi
from scrapy.utils.project import get_project_settings


class MhHeaderImagePipeline(ImagesPipeline):
    """头图下载处理"""
    def file_path(self, request, response=None, info=None):
        # 设置图片保存路径
        item = request.meta['item']
        _main = item['m_id']
        _main_file = re.sub(r'[？\\*|“<>:/]', '', str(_main))
        image_guid = request.url.split('/')[-1]
        filename = u'manhu/{0}/{1}'.format(_main_file, image_guid)
        return filename

    def get_media_requests(self, item, info):
        # 找到头图地址
        if not isinstance(item, MhInfoItem):
            return
        for image_url in item['m_image']:
            referer = image_url  # 处理防盗链
            yield Request(url=image_url, meta={'item': item}, headers={'referer': item['referer']})

    def item_completed(self, results, item, info):
        """完成 设置头图成字符串 """
        if isinstance(item, MhInfoItem):
            image_path = [value['path'] for ok, value in results if ok]
            if image_path:
                item['m_image'] = image_path[0]
        return item


class MhSectionImagePipeline(ImagesPipeline):
    """内容图片下载处理"""
    def file_path(self, request, response=None, info=None):
        # 设置图片保存路径
        item = request.meta['item']
        _main = item['m_id']
        _section = item['m_section_id']
        image_guid = request.url.split('/')[-1]
        filename = u'manhu/{0}/{1}/{2}'.format(_main, _section, image_guid)
        return filename

    def get_media_requests(self, item, info):
        # 找到头图地址
        if not isinstance(item, MhImageItem):
            return
        for image_url in item['m_image']:
            referer = image_url  # 处理防盗链
            yield Request(url=image_url, meta={'item': item}, headers={'referer': item['referer']})

    def item_completed(self, results, item, info):
        """完成 设置头图成字符串 """
        if isinstance(item, MhImageItem):
            image_path = [value['path'] for ok, value in results if ok]
            if image_path:
                item['m_image'] = image_path[0]
        return item


class MysqlPipeline(object):
    dbpool = None

    def __init__(self):
        if self.dbpool is None:
            # 获取setting文件中的配置
            settings = get_project_settings()
            conf = dict(
                host=settings['MYSQL_HOST'],
                user=settings['MYSQL_USER'],
                passwd=settings['MYSQL_PASSWORD'],
                db=settings['MYSQL_DB'],
                charset=settings['MYSQL_CHARSET'],
                port=settings['MYSQL_PORT'],
                use_unicode=settings['MYSQL_USE_UNICODE'],
                cursorclass=MySQLdb.cursors.DictCursor
            )
            self.dbpool = adbapi.ConnectionPool('MySQLdb', **conf)

    def close_spider(self, spider):
        self.dbpool.close()

    def process_item(self, item, spider):
        if isinstance(item, CategoryItem):
            self.dbpool.runInteraction(self._insert_mh_cates, item).addCallback(self.handle_error, item, spider)
            return item
        if isinstance(item, MhInfoItem):
            self.dbpool.runInteraction(self._insert_mh_info, item).addCallback(self.handle_error, item, spider)
            return item
        if isinstance(item, MhUpdateItem):
            self.dbpool.runInteraction(self._insert_mh_updates, item).addCallback(self.handle_error, item, spider)
            return item
        if isinstance(item, MhSectionInfoItem):
            self.dbpool.runInteraction(self._insert_mh_section, item).addCallback(self.handle_error, item, spider)
            return item
        if isinstance(item, MhImageItem):
            self.dbpool.runInteraction(self._insert_mh_detail, item).addCallback(self.handle_error, item, spider)
            return item

    def handle_error(self, failure, item, spider):
        # 处理异步插入的异常
        pass

    def _insert_mh_cates(self, cursor, item):
        """数据写入类型表"""
        has = cursor.execute('select * from categorys where cate_name = %s', (item['m_cate_name'],))
        if has == 0:
            _sql = """
                insert into categorys(cate_id, cate_name) 
                          values (%s, %s)
            """
            cursor.execute(_sql, (
                item['m_cate_id'], item['m_cate_name'])
            )
        else:
            return

    def _insert_mh_info(self, cursor, item):
        """数据写入漫画基本信息表"""
        has = cursor.execute('select * from info where mid = %s', (item['m_id'],))
        if has == 0:
            _sql = """
                insert into info(create_time, status, mid, title, category, arthor, description, area, image) 
                          values (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            cursor.execute(_sql, (
                item['m_create_time'], item['m_status'], item['m_id'],
                item['m_title'], item['m_category'],item['m_arthor'],
                item['m_description'],item['m_area'],item['m_image'])
            )
        else:
            return

    def _insert_mh_updates(self, cursor, item):
        """数据写入漫画更新表"""
        has = cursor.execute('select * from updates where mid = %s', (item['m_id'],))
        if has == 0:
            # 写入新数据
            _sql = """
                insert into updates(create_time, status, mid, update_time, state, latest) 
                          values (%s, %s, %s, %s, %s, %s)
            """
            cursor.execute(_sql, (
                item['m_create_time'], item['m_status'], item['m_id'],
                item['m_update_time'], item['m_state'], item['m_latest']))
        else:
            res = cursor.execute('select update_time from updates where mid = %s', (item['m_id'],))
            row = cursor.fetchone() if res == 1 else None
            if row['update_time'] != item['m_update_time']:
                # 更新数据
                cursor.execute('update updates set state = %s, update_time = %s, latest = %s where mid = %s',
                                (item['m_state'], item['m_update_time'], item['m_latest'], item['m_id'])
                               )
            else:
                return

    def _insert_mh_section(self, cursor, item):
        """数据写入漫画章节表"""
        has = cursor.execute('select * from section where mid=%s and sid=%s', (item['m_id'], item['m_section_id']))
        if has == 0:
            # 写入新数据
            _sql = """
                   insert into section(create_time, status, mid, sid, title, copyright) 
                             values (%s, %s, %s, %s, %s, %s)
               """
            cursor.execute(_sql, (
                item['m_create_time'], item['m_status'], item['m_id'],
                item['m_section_id'], item['m_title'], item['m_copyright']))
        else:
            return

    def _insert_mh_detail(self, cursor, item):
        """数据写入漫画图片表"""
        has = cursor.execute('select * from detail where sid = %s and pid=%s',
                             (item['m_section_id'], item['m_image_id']))
        if has == 0:
            _sql = """
                insert into detail(create_time, status, mid, sid, pid, image) 
                          values (%s, %s, %s, %s, %s, %s)
            """
            cursor.execute(_sql, (
                item['m_create_time'], item['m_status'], item['m_id'],
                item['m_section_id'], item['m_image_id'], item['m_image'])
           )
        else:
            return

